﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.Composition;
using Core.DataAccess.IRepositories;

namespace Core.DataAccess.Repositories
{
    [Export(typeof(ISchoolRepository))]
    public class SchoolRepository : ISchoolRepository
    {
        private Connection conn;

        public SchoolRepository()
        {
            conn = new Connection();
        }

        public void SaveSchool(School school)
        {
            using (MXHEntities context = new MXHEntities())
            {
                if (school.SchoolID > 0)
                {
                    context.Schools.Attach(new School() { SchoolID = school.SchoolID });
                    context.Schools.ApplyCurrentValues(school);
                }
                else
                {
                    context.Schools.AddObject(school);
                }
                context.SaveChanges();
            }
        }

        public List<School> GetSchoolsByAccountID(Int32 accountID)
        {
            using (MXHEntities context = new MXHEntities())
            {
                var schools = from s in context.Schools
                                  join ass in context.AccountSchools on s.SchoolID equals ass.SchoolID
                                  join a in context.Accounts on ass.AccountID equals a.AccountID
                                  where a.AccountID == accountID
                                  select s;
                List<School> lstSchool = schools.Count() > 0 ? schools.ToList() : null;
                return lstSchool;
            }
        }

        public School GetSchoolBySchoolID(Int32 schoolID)
        {
            using (MXHEntities context = new MXHEntities())
            {
                var s = from sch in context.Schools
                             where sch.SchoolID == schoolID
                             select sch;
                School school = s != null ? s.FirstOrDefault() : null;
                return school;
            }
        }

        public School GetSchoolBySchoolName(string schoolName)
        {
            using (MXHEntities context = new MXHEntities())
            {
                var s = from sch in context.Schools
                        where sch.SchoolName == schoolName
                        select sch;
                School school = s != null ? s.FirstOrDefault() : null;
                return school;
            }
        }

        public void SaveAccountSchool(AccountSchool accSch)
        {
            using (MXHEntities context = new MXHEntities())
            {
                if (accSch.ASID > 0)
                {
                    context.AccountSchools.Attach(new AccountSchool() { ASID = accSch.ASID });
                    context.AccountSchools.ApplyCurrentValues(accSch);
                }
                else
                {
                    context.AccountSchools.AddObject(accSch);
                }
                context.SaveChanges();
            }
        }

        public List<AccountSchool> GetAccountSchoolListByUsername(string username)
        {
            using (MXHEntities context = new MXHEntities())
            {
                var schools = from s in context.AccountSchools.Include("School")
                              join sc in context.Schools on s.SchoolID equals sc.SchoolID
                              join a in context.Accounts on s.AccountID equals a.AccountID
                              where a.Username == username
                              select s;
                List<AccountSchool> lst = schools.Count() > 0 ? schools.ToList() : null;
                return lst;
            }
        }

        public List<AccountSchool> GetAccountSchoolListByAccountID(Int32 accountID)
        {            
            using (MXHEntities context = new MXHEntities())
            {
                var s = from sch in context.AccountSchools
                        where sch.AccountID == accountID
                        select sch;
                List<AccountSchool> lst = s.Count() > 0 ? s.ToList() : null;                
                return lst;
            }
        }

        public List<Account> GetAllFriendsSchoolByAccountID(int accountID, string schoolName)
        {
            List<Account> lstAccount = new List<Account>();            
            using (MXHEntities context = conn.GetContext())
            {
                var accounts = (from acc in context.Accounts
                               join asch in context.AccountSchools on acc.AccountID equals asch.AccountID
                               join sch in context.Schools on asch.SchoolID equals sch.SchoolID
                               where sch.SchoolName.Contains(schoolName)
                               select acc).Distinct();               
                lstAccount = accounts.ToList();                
                int index = lstAccount.FindIndex(n => n.AccountID == accountID);
                lstAccount.RemoveAt(index);                
            }
            return lstAccount;
        }

        public int UpdateSchoolInfo(int accountID,School school)
        {
            using (MXHEntities context = conn.GetContext())
            {
                var item = from s in context.Schools
                           join accSch in context.AccountSchools on s.SchoolID equals accSch.SchoolID
                           where accSch.AccountID == accountID                                 
                           select s;
                if (item.Count() > 0)
                {
                    School sch = item.FirstOrDefault();
                    sch.SchoolName = school.SchoolName;
                    sch.Grade = school.Grade;
                    sch.City = school.City;
                    context.SaveChanges();
                    return 0;
                }
                // if not exists School -> Create new                
                context.Schools.AddObject(school);
                context.SaveChanges();
                return school.SchoolID;
            }
        }

        public int UpdateAccountSchoolInfo(int accountID, AccountSchool accSchool,int schoolID)
        {
            using (MXHEntities context = conn.GetContext())
            {
                var item = from asch in context.AccountSchools
                           where asch.AccountID == accountID
                           select asch;
                if (item.Count() > 0)
                {
                    AccountSchool accountSchool = item.FirstOrDefault();
                    accountSchool.StartYear = accSchool.StartYear;
                    accountSchool.GraduationYear = accSchool.GraduationYear;
                    context.SaveChanges();
                    return 0;
                }
                // if not exists AccountSchool -> Create new
                AccountSchool newAccSchool = new AccountSchool();
                newAccSchool.AccountID = accountID;
                newAccSchool.SchoolID = schoolID;
                newAccSchool.StartYear = accSchool.StartYear;
                newAccSchool.GraduationYear = accSchool.GraduationYear;
                context.AccountSchools.AddObject(newAccSchool);
                context.SaveChanges();
                return 1;
            }
        }
    }
}
